﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Text;

namespace RedemptoristRetreatCenter.DatabaseUtility
{
    /// <summary>
    /// The volunteer utility class connects with the the RedemptoristRetreatCenter Database and uses SQL to grab information pertaining to volunteers
    /// </summary>
    public class VolunteerUtility
    {
        private SqlConnection rrcDbConn;
        
        /// <summary>
        /// Volunteer Utility Default constructor, creates connection to Redemptorist Retreat Database
        /// </summary>
        public VolunteerUtility()
        {
            rrcDbConn = new SqlConnection();
            rrcDbConn.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }

        /// <summary>
        /// Opens connection between Volunteer Utility and the Redemptorist Retreat Database
        /// </summary>
        private void openConnection()
        {
            try
            {
                rrcDbConn.Open();
                Console.WriteLine(Environment.NewLine + "rrcDbConn.State: " + rrcDbConn.State.ToString());
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error opening connecdtion: " + ex.ToString());
            }
        }

        /// <summary>
        /// Closes connection between Volunteer Utility and the Redemptorist Retreat Database
        /// </summary>
        public void closeConnection()
        {
            rrcDbConn.Close();
            Console.WriteLine("rrcDbConn.State: " + rrcDbConn.State.ToString());
        }

        /// <summary>
        /// Adds a list of volunteer tasks that a particular user has selected and returns true/false if the record was successfully added
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="volunteerTasks"></param>
        /// <returns>Boolean</returns>
        public Boolean AddVolunteerTasks(int userID, List<String>volunteerTasks)
        {
            // -- Adding the list of tasks to the VolTaskXRef Table --
            for (int task = 0; task < volunteerTasks.Count; task++)
            {
                int taskID = -1;

                // -- looking up the task's taskID by the task's short description --
                taskID = findVolTaskIDByShortDesc(volunteerTasks[task]);

                // -- deciding whether the task exists --
                if (taskID != -1)
                {
                    // -- creating the volunteer task in the TaskXRefRecord table -- 
                    createVolTaskXRefRecord(userID, taskID);
                }
                else
                    return false;
            }
            return true;
        }

        /// <summary>
        /// Selecting all of a user's volunteer tasks from the VolunteerTaskXref table, returns a list of the volunteer taskIds
        /// </summary>
        /// <param name="userID"></param>
        /// <returns>List<int></returns>
        public List<int> getVolTaskIdsForUser(int userID)
        {
            List<int> taskIds = new List<int>();

            // -- SQL to select the tasks for the user --
            StringBuilder sqlText = new StringBuilder("SELECT taskId ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.VolunteerTask ");
            sqlText.Append("WHERE activeData=1");
            sqlText.Append(userID);
            sqlText.Append(";");

            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            SqlDataReader volAccountReader = null;
            volAccountReader = sqlCmd.ExecuteReader();

            while (volAccountReader.Read())
            {
                // -- adding taskID found with the SQL to the list to be returned --
                taskIds.Add((int)volAccountReader["taskId"]);
            }

            closeConnection();

            return taskIds;
        }

        /// <summary>
        /// Looks up a task by taskId and returns the task's short description
        /// </summary>
        /// <param name="taskId"></param>
        /// <returns>String</returns>
        public String getVolTaskShortDescByTaskID(int taskId)
        {
            String shortDesc = "";

            // -- SQL to find a task's short description by the task's taskID --
            StringBuilder sqlText = new StringBuilder("SELECT shortDescription ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.VolunteerTaskXref ");
            sqlText.Append("WHERE activeData=1 AND taskId='");
            sqlText.Append(taskId);
            sqlText.Append("';");

            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            SqlDataReader volAccountReader = null;
            volAccountReader = sqlCmd.ExecuteReader();

            // -- executing SQL to select the short description --
            while (volAccountReader.Read())
            {
                shortDesc = (String)volAccountReader["shortDescription"];
            }

            closeConnection();

            // -- returning a formatted form of the short description for readability --
            if(shortDesc.Equals("BulkMailing"))
                return "Bulk Mailing";
            else if(shortDesc.Equals("BushRemoval"))
                return "Bush Removal";
            else if(shortDesc.Equals("ComputerMaintenance"))
                return "Computer Maintenance";
            else if(shortDesc.Equals("PlantCare"))
                return "Plant Care";
            else if(shortDesc.Equals("DataEntry"))
                return "Data Entry";
            else if(shortDesc.Equals("LeafRaking"))
                return "Leaf Raking";
            else if(shortDesc.Equals("GeneralOffice"))
                return "General Office";
            else if(shortDesc.Equals("WordProcessing"))
                return "Word Processing";
            else if(shortDesc.Equals("RoofingGutters"))
                return "Roofing and Gutters";
            else if(shortDesc.Equals("RoadRepair"))
                return "Road Repair";
            else if(shortDesc.Equals("EngineRepair"))
                return "Small Engine Repair";
            else if(shortDesc.Equals("WindowWashing"))
                return "Window Washing";
            else
                return shortDesc;
        }

        /// <summary>
        /// Looks up a task by short description and returns the task's taskId 
        /// </summary>
        /// <param name="shortDesc"></param>
        /// <returns>int</returns>
        public int findVolTaskIDByShortDesc(string shortDesc)
        {
            int taskID = -1;

            // -- SQL to look up a task's taskId by the task's short description --
            StringBuilder sqlText = new StringBuilder("SELECT taskId ");
            sqlText.Append("FROM RedemptoristRetreat.dbo.VolunteerTask ");
            sqlText.Append("WHERE activeData=1 AND shortDescription='");
            sqlText.Append(shortDesc);
            sqlText.Append("';");

            SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            SqlDataReader volAccountReader = null;
            volAccountReader = sqlCmd.ExecuteReader();
            
            // -- executing SQL to find the taskId --
            while (volAccountReader.Read())
            {
                taskID = (int)volAccountReader["taskId"];
            }

            closeConnection();

            return taskID;
        }

        /// <summary>
        /// Creating a volunteer task record on the VolTaskXRefRecord
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="taskId"></param>
        /// <returns>Boolean</returns>
        public Boolean createVolTaskXRefRecord(int userId, int taskId)
        {
            if (rrcDbConn.State != ConnectionState.Open)
            {
                openConnection();
            }

            int rowsAffected = 0;

            try
            {
                // -- SQL to create a volunteer task record on the VolunteerTaskXref table --
                StringBuilder sqlText = new StringBuilder("INSERT INTO VolunteerTaskXref (userId, taskId, activeData) ");
                sqlText.Append(String.Format("VALUES ({0},{1},{2});", userId, taskId, 1));

                SqlCommand sqlCmd = new SqlCommand(sqlText.ToString(), rrcDbConn);

                // -- executing SQL to insert record on VolunteerTaskXref table --
                rowsAffected = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to insert.  exception=" + ex.ToString());
                rowsAffected = -1;
            }
            finally
            {
                closeConnection();
            }

            // -- returns true if record was inserted, false if no change to the database --
            return (rowsAffected > 0);
        }
    }
}